!pip install -q -r https://raw.githubusercontent.com/snowflakedb/snowflake-connector-python/v2.7.3/tested_requirements/requirements_39.reqs
!export TMPDIR=/large_tmp_volume
# %env TMPDIR=/drive/My Drive/Personal/Snowflake
import pandas as pd
import json
import geopandas as gpd
from shapely import wkt
from shapely.geometry import Polygon, shape
from folium import Map, Marker, GeoJson
import branca.colormap as cm
import geojson
from geojson.feature import *
from geojson import Feature, FeatureCollection
import snowflake.connector
f = open('credentials.json')
data = json.load(f)
f.close()
conn = snowflake.connector.connect(
user=data["user"],
password=data["password"],
account=data["account"]
)
cur = conn.cursor()
cur.execute("create or replace database geo_playground;")
cur.execute("use schema geo_playground.public")
cur.execute("create or replace view spain_h3 as \
(select g.GEOID, g.GEOM, d.POPULATION \
from SPAIN_H3.CARTO.DERIVED_SPATIALFEATURES_ESP_H3RES8_V1_YEARLY_V2 d \
join SPAIN_H3.CARTO.GEOGRAPHY_ESP_H3RES8_V1 g on g.GEOID = d.GEOID);")
cur.execute("select * from spain_h3 where POPULATION > 500")
df = cur.fetch_pandas_all()
df.head()
| GEOID | GEOM | POPULATION | |
|---|---|---|---|
| 0 | 8839446065fffff | {\n "coordinates": [\n [\n [\n ... | 2563.029698 |
| 1 | 88392d1ab1fffff | {\n "coordinates": [\n [\n [\n ... | 681.371578 |
| 2 | 88184b8d91fffff | {\n "coordinates": [\n [\n [\n ... | 776.731446 |
| 3 | 8839446321fffff | {\n "coordinates": [\n [\n [\n ... | 4810.222257 |
| 4 | 883925cc2dfffff | {\n "coordinates": [\n [\n [\n ... | 954.065804 |
df['GEOM'] = df['GEOM'].apply(lambda x: geojson.loads(x))
def dataframe_to_geojson(df_geo, column_name, file_output = None):
'''Produce the GeoJSON for a dataframe that has a geometry column in geojson format already'''
list_features = []
for i,row in df_geo.iterrows():
feature = Feature(geometry = row["GEOM"], properties = {column_name : row[column_name]})
list_features.append(feature)
feat_collection = FeatureCollection(list_features)
geojson_result = json.dumps(feat_collection)
#optionally write to file
if file_output is not None:
with open(file_output,"w") as f:
json.dump(feat_collection,f)
return geojson_result
def choropleth_map(df_aggreg, column_name, border_color = 'none', fill_opacity = 0.5, initial_map = None, with_legend = True,
kind = "linear"):
#colormap
min_value = df_aggreg[column_name].min()
max_value = df_aggreg[column_name].max()
m = round ((min_value + max_value ) / 2 , 0)
if initial_map is None:
initial_map = Map(location= [41.390333,2.1647773], zoom_start=11, tiles="cartodbpositron")
#the colormap
#color names accepted https://github.com/python-visualization/branca/blob/master/branca/_cnames.json
if kind == "linear":
custom_cm = cm.LinearColormap(['gray','blue','green','yellow','orange','red'], vmin=min_value, vmax=max_value)
elif kind == "outlier":
#for outliers, values would be -11,0,1
custom_cm = cm.LinearColormap(['white','gray','black'], vmin=min_value, vmax=max_value)
elif kind == "filled_nulls":
custom_cm = cm.LinearColormap(['sienna','green','yellow','red'],
index=[0,min_value,m,max_value],vmin=min_value,vmax=max_value)
#create geojson data from dataframe
geojson_data = dataframe_to_geojson(df_geo = df_aggreg, column_name = column_name)
#plot on map
name_layer = "Choropleth "
if kind != "linear":
name_layer = name_layer + kind
GeoJson(
geojson_data,
style_function=lambda feature: {
'fillColor': custom_cm(feature['properties'][column_name]),
'color': border_color,
'weight': 1,
'fillOpacity': fill_opacity
},
name = name_layer
).add_to(initial_map)
#add legend (not recommended if multiple layers)
if with_legend == True:
custom_cm.add_to(initial_map)
return initial_map
map = choropleth_map(df_aggreg = df, column_name = "POPULATION")
map
len(df)
16488